﻿using System;
using System.Data;
using System.Data.SqlClient;
using AccessControl.Common;


namespace AccessControl.DataAcess
{
	public class DeviceAccess
	{
        
		public AccessControlDS.tblDeviceDataTable SelectAll(AccessControlDS.tblDeviceDataTable tbl)
		{
			using (var cn = new SqlConnection(Util.ConnectionString))
			{
				var cmdSelect = new SqlCommand("SELECT * FROM tblDevice", cn);
				try
				{
					cn.Open();

					var reader = cmdSelect.ExecuteReader();
					while (reader.Read())
					{
					    var newRow = tbl.NewtblDeviceRow();
					    newRow.pkDevice = (int)reader["pkDevice"];
					    newRow.DeviceName = (String)reader["DeviceName"];
					    newRow.DeviceIP = (String)reader["DeviceIP"];
					    newRow.DevicePort = (Int32)reader["DevicePort"];
					    newRow.DeviceType = (byte)reader["DeviceType"];
					    newRow.fkWorkflow = (int)reader["fkWorkflow"];
					    tbl.AddtblDeviceRow(newRow);
					}
				}
				finally
				{
					cn.Close();
				}
				return tbl.Rows.Count != 0 ? tbl : null;
			}
		}

        public AccessControlDS.tblDeviceDataTable SelectAllEnabled(AccessControlDS.tblDeviceDataTable tbl)
		{
			using (var cn = new SqlConnection(Util.ConnectionString))
			{
                var cmdSelect = new SqlCommand("SELECT * FROM tblDevice JOIN tblWorkflow ON tblWorkflow.pkWorkflow=tblDevice.fkWorkflow WHERE tblWorkflow.Enabled=1 ", cn);
				try
				{
					cn.Open();

					var reader = cmdSelect.ExecuteReader();
					while (reader.Read())
					{
					    var newRow = tbl.NewtblDeviceRow();
					    newRow.pkDevice = (int)reader["pkDevice"];
					    newRow.DeviceName = (String)reader["DeviceName"];
					    newRow.DeviceIP = (String)reader["DeviceIP"];
					    newRow.DevicePort = (Int32)reader["DevicePort"];
					    newRow.DeviceType = (byte)reader["DeviceType"];
					    newRow.fkWorkflow = (int)reader["fkWorkflow"];
					    tbl.AddtblDeviceRow(newRow);
					}
				}
				finally
				{
					cn.Close();
				}
				return tbl.Rows.Count != 0 ? tbl : null;
			}
		}

		public AccessControlDS.tblDeviceDataTable SelectByFilter(string filter)
		{
			using (var cn = new SqlConnection(Util.ConnectionString))
			{
				var cmdSelect = new SqlCommand(String.Format("SELECT * FROM tblDevice WHERE {0}", filter), cn);
				var tblDeviceTable = new AccessControlDS.tblDeviceDataTable();
				try
				{
					cn.Open();
					var reader = cmdSelect.ExecuteReader();
					while (reader.Read())
					{
					    var newRow = tblDeviceTable.NewtblDeviceRow();
					    newRow.pkDevice = (int)reader["pkDevice"];
					    newRow.DeviceName = (String)reader["DeviceName"];
					    newRow.DeviceIP = (String)reader["DeviceIP"];
					    newRow.DevicePort = (Int32)reader["DevicePort"];
					    newRow.DeviceType = (byte)reader["DeviceType"];
					    newRow.fkWorkflow = (int) reader["fkWorkflow"];
					    tblDeviceTable.AddtblDeviceRow(newRow);
					}
				}
				finally
				{
					cn.Close();
				}
				return tblDeviceTable.Rows.Count != 0 ? tblDeviceTable : null;
			}
		}

		public void Insert(AccessControlDS.tblDeviceRow row)
		{
			using (var cn = new SqlConnection(Util.ConnectionString))
			{
				var cmdInsert = new SqlCommand("spDevice_Insert", cn) { CommandType = CommandType.StoredProcedure };
				cmdInsert.Parameters.AddWithValue("@DeviceName", row.DeviceName);
				cmdInsert.Parameters.AddWithValue("@DeviceIP", row.DeviceIP);
                cmdInsert.Parameters.AddWithValue("@DevicePort", row.DevicePort);
                cmdInsert.Parameters.AddWithValue("@DeviceType", row.DeviceType);
                cmdInsert.Parameters.AddWithValue("@fkWorkflow", row.fkWorkflow);
                try
				{
					cn.Open();
					cmdInsert.ExecuteNonQuery();
				}
				finally
				{
					cn.Close();
				}
			}
		}

		public void Update(AccessControlDS.tblDeviceRow row)
		{
			using (var cn = new SqlConnection(Util.ConnectionString))
			{
				var cmdUpdate = new SqlCommand("spDevice_Update", cn) { CommandType = CommandType.StoredProcedure };
				cmdUpdate.Parameters.AddWithValue("@pkDevice", row.pkDevice);
				cmdUpdate.Parameters.AddWithValue("@DeviceName", row.DeviceName);
				cmdUpdate.Parameters.AddWithValue("@DeviceIP", row.DeviceIP);
                cmdUpdate.Parameters.AddWithValue("@DevicePort", row.DevicePort);
                cmdUpdate.Parameters.AddWithValue("@fkWorkflow", row.fkWorkflow);
                try
				{
					cn.Open();
					cmdUpdate.ExecuteNonQuery();
				}
				finally
				{
					cn.Close();
				}
			}
		}

		public void Delete(AccessControlDS.tblDeviceRow row)
		{
			using (var cn = new SqlConnection(Util.ConnectionString))
			{
				var cmdDelete = new SqlCommand("spDevice_Delete", cn) { CommandType = CommandType.StoredProcedure };
				cmdDelete.Parameters.AddWithValue("@pkDevice", row.pkDevice);
				try
				{
					cn.Open();
					cmdDelete.ExecuteNonQuery();
				}
				finally
				{
					cn.Close();
				}
			}
		}

	}
}
